package org.study.jdbc.api.dao;

import cn.hutool.core.util.IdUtil;
import com.google.common.collect.Lists;
import com.mysql.cj.jdbc.*;
import lombok.extern.slf4j.Slf4j;
import org.study.jdbc.api.constant.DbProperties;
import org.study.jdbc.api.model.Student;
import org.study.jdbc.api.util.JdbcUtils;

import javax.sql.XAConnection;
import javax.transaction.xa.*;
import java.sql.*;
import java.util.List;

/**
 * 类的描述
 *
 * @author Administrator
 * @date 2022-11-01
 */
@Slf4j
public class StudentDao {
    public int insertWithId(Student student) {
        int insert = 0;
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement ps = conn.prepareStatement("INSERT INTO t_student (id, name, gender, grade, score) VALUES (?, ?, ?, ?, ?)")) {
            // 注意：索引从1开始
            /*ps.setObject(1, student.getId());
            ps.setObject(2, student.getName());
            ps.setObject(3, student.getGender());
            ps.setObject(4, student.getGrade());
            ps.setObject(5, student.getScore());*/

            ps.setLong(1, student.getId());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getGender());
            ps.setInt(4, student.getGrade());
            ps.setDouble(5, student.getScore());
            // 返回新增的行数
            insert = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("新增数据异常", e);
        }
        return insert;
    }

    public int[] insertBatch(List<Student> studentList) {
        int[] insert = new int[studentList.size()];
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement ps = conn.prepareStatement("INSERT INTO t_student (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
            for (Student student : studentList) {
                // 注意：索引从1开始
                ps.setString(1, student.getName());
                ps.setInt(2, student.getGender());
                ps.setInt(3, student.getGrade());
                ps.setDouble(4, student.getScore());
                ps.addBatch();
            }
            // 返回新增的行数
            insert = ps.executeBatch();
        } catch (SQLException e) {
            log.error("新增数据异常", e);
        }
        return insert;
    }

    public int insertWithoutId(Student student) {
        int insert = 0;
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement ps = conn.prepareStatement("INSERT INTO t_student (name, gender, grade, score) VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
            // 注意：索引从1开始
            /*ps.setObject(1, student.getName());
            ps.setObject(2, student.getGender());
            ps.setObject(3, student.getGrade());
            ps.setObject(4, student.getScore());*/

            ps.setString(1, student.getName());
            ps.setInt(2, student.getGender());
            ps.setInt(3, student.getGrade());
            ps.setDouble(4, student.getScore());
            // 返回新增的行数
            insert = ps.executeUpdate();
            try (ResultSet rs = ps.getGeneratedKeys()) {
                while (rs.next()) {
                    // 注意：索引从1开始
                    long id = rs.getLong(1);
                    log.info("新增数据的 id={}", id);
                }
            }
        } catch (SQLException e) {
            log.error("新增数据异常", e);
        }
        return insert;
    }

    public int deleteById(Long id) {
        int delete = 0;
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement ps = conn.prepareStatement("DELETE FROM t_student WHERE id = ?")) {
            // 注意：索引从1开始
            ps.setObject(1, id);
            // 返回删除的行数
            delete = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("删除数据异常", e);
        }
        return delete;
    }

    public int updateNameById(Student student) {
        int update = 0;
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement ps = conn.prepareStatement("UPDATE t_student SET name = ? WHERE id = ?")) {
            // 注意：索引从1开始
            ps.setObject(1, student.getName());
            ps.setObject(2, student.getId());
            // 返回更新的行数
            update = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("更新数据异常", e);
        }
        return update;
    }

    public List<Student> selectAll() {
        List<Student> list = Lists.newArrayList();
        String sql = "SELECT id, name, gender, grade, score FROM t_student ORDER BY id DESC";
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             Statement statement = conn.createStatement();
             ResultSet rs = statement.executeQuery(sql)) {
            while (rs.next()) {
                Student student = new Student();
                // 注意：索引从1开始
                student.setId(rs.getObject(1) == null ? null : rs.getLong(1));
                student.setName(rs.getObject(2) == null ? null : rs.getString(2));
                student.setGender(rs.getObject(3) == null ? null : rs.getInt(3));
                student.setGrade(rs.getObject(4) == null ? null : rs.getInt(4));
                student.setScore(rs.getObject(5) == null ? null : rs.getDouble(5));
                list.add(student);
            }
        } catch (SQLException e) {
            log.error("查询数据异常", e);
        }
        return list;
    }

    public List<Student> selectByName(String queryName) {
        List<Student> list = Lists.newArrayList();
        String sql = "SELECT id, name, gender, grade, score FROM t_student WHERE name LIKE ? ORDER BY id DESC";
        try (Connection conn = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             PreparedStatement statement = conn.prepareStatement(sql)) {
            // 注意：索引从1开始
            statement.setObject(1, "%" + queryName + "%");
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    Student student = new Student();
                    // 注意：索引从1开始
                    student.setId(rs.getObject(1) == null ? null : rs.getLong(1));
                    student.setName(rs.getObject(2) == null ? null : rs.getString(2));
                    student.setGender(rs.getObject(3) == null ? null : rs.getInt(3));
                    student.setGrade(rs.getObject(4) == null ? null : rs.getInt(4));
                    student.setScore(rs.getObject(5) == null ? null : rs.getDouble(5));
                    list.add(student);
                }
            }
        } catch (SQLException e) {
            log.error("查询数据异常", e);
        }
        return list;
    }

    public int transactionWithoutXa(Student student4Insert, Student student4Update, int exceptionFlag) {
        try (Connection conn01 = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             Connection conn02 = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password)) {
            try (PreparedStatement ps01 = conn01.prepareStatement("INSERT INTO t_student (id, name, gender, grade, score) VALUES (?, ?, ?, ?, ?)")) {
                // 注意：索引从1开始
                ps01.setLong(1, student4Insert.getId());
                ps01.setString(2, student4Insert.getName());
                ps01.setInt(3, student4Insert.getGender());
                ps01.setInt(4, student4Insert.getGrade());
                ps01.setDouble(5, student4Insert.getScore());
                // 返回新增的行数
                // ps01.execute();
                int insert = ps01.executeUpdate();
                log.info("新增结果：{}", insert);
            }
            if (exceptionFlag == 1) {
                throw new RuntimeException("模拟异常：两次事务操作之间");
            }
            try (PreparedStatement ps02 = conn02.prepareStatement("UPDATE t_student SET name = ? WHERE id = ?")) {
                // 注意：索引从1开始
                ps02.setObject(1, student4Update.getName());
                ps02.setObject(2, student4Update.getId());
                // 返回更新的行数
                // ps02.execute();
                int update = ps02.executeUpdate();
                log.info("修改结果：{}", update);
            }
            if (exceptionFlag == 2) {
                throw new RuntimeException("模拟异常：两次事务操作之后");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 1;
    }

    public int transactionWithXa(Student student4Insert, Student student4Update, boolean isException) {
        // 打印 XA 日志
        boolean isWriteXaLog = true;
        try (Connection conn01 = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password);
             Connection conn02 = JdbcUtils.getConnection(DbProperties.dbStudent, DbProperties.username, DbProperties.password)) {
            XAConnection xaConn01 = new MysqlXAConnection((JdbcConnection) conn01, isWriteXaLog);
            XAConnection xaConn02 = new MysqlXAConnection((JdbcConnection) conn02, isWriteXaLog);
            // 分别获得资源管理器操作接口示例RM
            XAResource xaResource01 = xaConn01.getXAResource();
            XAResource xaResource02 = xaConn02.getXAResource();

            // 应用程序请求事务管理器执行一个分布式事务，事务管理器生成的全局事务id
            byte[] gtrid = IdUtil.randomUUID().getBytes();
            int formatId = 1;

            Xid xid01 = new MysqlXid(gtrid, "bqual01".getBytes(), formatId);
            Xid xid02 = new MysqlXid(gtrid, "bqual02".getBytes(), formatId);
            try {
                // ==================== 执行分支事务
                // 执行分支事务1
                xaResource01.start(xid01, XAResource.TMNOFLAGS);
                try (PreparedStatement ps01 = conn01.prepareStatement("INSERT INTO t_student (id, name, gender, grade, score) VALUES (?, ?, ?, ?, ?)")) {
                    // 注意：索引从1开始
                    ps01.setLong(1, student4Insert.getId());
                    ps01.setString(2, student4Insert.getName());
                    ps01.setInt(3, student4Insert.getGender());
                    ps01.setInt(4, student4Insert.getGrade());
                    ps01.setDouble(5, student4Insert.getScore());
                    // 返回新增的行数
                    // ps01.execute();
                    int insert = ps01.executeUpdate();
                    log.info("新增结果：{}", insert);
                }
                xaResource01.end(xid01, XAResource.TMSUCCESS);
                if (isException) {
                    throw new RuntimeException("模拟异常：两次事务操作之间");
                }
                // 执行分支事务2
                xaResource02.start(xid02, XAResource.TMNOFLAGS);
                try (PreparedStatement ps02 = conn02.prepareStatement("UPDATE t_student SET name = ? WHERE id = ?")) {
                    // 注意：索引从1开始
                    ps02.setObject(1, student4Update.getName());
                    ps02.setObject(2, student4Update.getId());
                    // 返回更新的行数
                    // ps02.execute();
                    int update = ps02.executeUpdate();
                    log.info("修改结果：{}", update);
                }
                xaResource02.end(xid02, XAResource.TMSUCCESS);

                // ==================== 两阶段提交
                // 第一阶段：通知所有的资源管理器准备提交分支事务
                int prepare01 = xaResource01.prepare(xid01);
                int prepare02 = xaResource02.prepare(xid02);
                // 第二阶段：提交或回滚所有分支事务
                boolean onePhase = false;
                if (prepare01 == XAResource.XA_OK && prepare02 == XAResource.XA_OK) {
                    // 所有分支事务都进入准备状态，则提交所有分支事务
                    xaResource01.commit(xid01, onePhase);
                    xaResource02.commit(xid02, onePhase);
                } else {
                    // 若有分支事务没有进入准备状态，责回滚所有分支事务
                    xaResource01.rollback(xid01);
                    xaResource02.rollback(xid02);
                }
            } catch (XAException e) {
                // 出现异常则回滚
                xaResource01.rollback(xid01);
                xaResource02.rollback(xid02);
                e.printStackTrace();
            } finally {
                if (xaConn01 != null) {
                    xaConn01.close();
                }
                if (xaConn02 != null) {
                    xaConn02.close();
                }
            }
        } catch (SQLException | XAException e) {
            e.printStackTrace();
        }
        return 1;
    }
}
